﻿namespace QLPhongKham.BaseClass
{
    using ConfigDatabase.Class;
    using Npgsql;
    using System;

    public class Database
    {
        private ConnectDatabase _condata;

        public Database()
        {
            this._condata = new ConnectDatabase();
        }

        public Database(ConnectDatabase condata)
        {
            this._condata = condata;
        }

        public void f_createTable_benhnhan(string schema)
        {
            string str2 = (((((((("" + "CREATE TABLE " + schema + ".bn_dmbacsi (id numeric(3,0) NOT NULL default 0,ma  varchar(6),ten  text,ghichu  text,stt  smallint  default 0, CONSTRAINT pk_bn_dmbacsi PRIMARY KEY (id));\n") + "CREATE TABLE " + schema + ".bn_dmbenhtat (id numeric(10,0) NOT NULL default 0,ma varchar(6),ten   text,ghichu text,stt smallint  default 0, CONSTRAINT pk_bn_dmbenhtat PRIMARY KEY (id));\n") + "CREATE TABLE " + schema + ".bn_dmdoituong (id   numeric(2,0) NOT NULL default 0,ten  text, CONSTRAINT pk_bn_dmdoituong PRIMARY KEY (id));\n") + "CREATE TABLE " + schema + ".bn_dmnhantu (id   numeric(1,0) NOT NULL default 0,ten  varchar(25), CONSTRAINT pk_bn_dmnhantu PRIMARY KEY (id));\n") + "CREATE TABLE " + schema + ".bn_dmphong (id numeric(2,0) NOT NULL default 0,ten  text,idvp  integer  default 0,ghichu  text,ma  varchar(10), CONSTRAINT pk_bn_dmphong PRIMARY KEY (id));\n") + "CREATE TABLE " + schema + ".bn_thongtinbn (mabn varchar(10) NOT NULL,hoten text,idgioitinh numeric(1,0)  default 0,namsinh  numeric(4,0)  default 0,ngaysinh varchar(10),diachi   text,dienthoai  varchar(30), CONSTRAINT pk_bn_thongtinbn PRIMARY KEY (mabn));\n") + "CREATE TABLE " + schema + ".bn_dsbnhen (id numeric(20,0) NOT NULL default 0,ma  varchar(10),ngay  timestamp without time zone  default now(),mabn  varchar(10),hoten  text,namsinh  numeric(4,0)  default 0,idgioitinh numeric(1,0)  default 0,diachi  text,dienthoai varchar(30),idphong  numeric(2,0)  default 0,stt smallint  default 0,lydo text,daden numeric(1,0)  default 0,ngaycapnhat timestamp without time zone  default now(), CONSTRAINT pk_bn_dsbnhen PRIMARY KEY (id));\n") + "CREATE TABLE " + schema + ".bn_dskhonghen (id numeric(20,0) NOT NULL default 0,tungay  timestamp without time zone  default now(),denngay  timestamp without time zone  default now(),lydo text,ngaycapnhat timestamp without time zone  default now(), CONSTRAINT pk_bn_dskhonghen PRIMARY KEY (id));\n") + "CREATE TABLE " + schema + ".bn_nhapvien (id  numeric(20,0) NOT NULL default 0,idql numeric(18,0) NOT NULL default 0,mabn varchar(10),idphong numeric(1,0)  default 0,ngay  timestamp without time zone,iddoituong  numeric(1,0)  default 0,idnhantu  numeric(1,0)  default 0,idtinhtrang numeric(1,0)  default 0,soluutru  varchar(20),cdnoichuyen  text,cdchinh text,cdkemtheo text,idbacsi  numeric(2,0)  default 0,idcdnoichuyen numeric(10,0)  default 0,idcdchinh numeric(10,0)  default 0,idcdkemtheo numeric(10,0)  default 0, CONSTRAINT pk_bn_nhapvien PRIMARY KEY (id));\n";
            str2 = str2 + "CREATE TABLE " + schema + ".bn_tiepdon (idql  numeric(20,0) NOT NULL default 0,mabn varchar(10),ngay  timestamp without time zone,iddoituong  numeric(1,0)  default 0,idphong  numeric(1,0)  default 0,stt numeric(3,0)  default 0,lydokham   text,mach varchar(5),nhietdo varchar(10),huyetap  varchar(10),cannang varchar(10),tuoivao varchar(4)  default 0,CONSTRAINT pk_" + schema + "_bn_tiepdon PRIMARY KEY (idql));\n";
            str2 = str2 + "CREATE TABLE " + schema + ".bn_thongtinbn_lienhe (idql numeric(20,0) NOT NULL,mabn varchar(10) NOT NULL,quanhe text,hoten  text,diachi text,dienthoai varchar(30), CONSTRAINT pk_" + schema + "_bn_thongtinbn_lienhe PRIMARY KEY (idql));\n";
            str2 = str2 + "CREATE TABLE " + schema + ".bn_chidinh (id  numeric(20,0) NOT NULL default 0,idql  numeric(20,0) NOT NULL default 0,mabn  varchar(10),ngay  timestamp without time zone,iddoituong  numeric(1,0)  default 0,idphong  numeric(1,0)  default 0,idvp   numeric(6,0)  default 0,soluong  numeric(10,2)  default 0,dongia  numeric(15,2)  default 0,dadongtien  numeric(1,0)  default 0,dathuchien  numeric(1,0)  default 0,ghichu text,idttvp varchar(200)  default '',sldathanhtoan  numeric(15,2)  default 0,CONSTRAINT pk_" + schema + "_bn_chidinh PRIMARY KEY (id));\n";
            string[] strArray = (str2 + "CREATE TABLE " + schema + ".bn_benhankb (idql numeric(20,0) NOT NULL default 0,mabn varchar(10),idphong numeric(1,0)  default 0,ngayvao timestamp without time zone,trieuchung  text,benhchinh  text,benhkemtheo   text,iddoituong numeric(1,0)  default 0,xutri varchar(20),ghichu  text,idbacsi  numeric(2,0)  default 0,idbenhchinh   numeric(10,0)  default 0,idbenhkemtheo  numeric(10,0)  default 0,  CONSTRAINT pk_" + schema + "_bn_benhankb PRIMARY KEY (idql));\n").Split(new char[] { ';' });
            for (int i = 0; i < strArray.Length; i++)
            {
                this.f_update(strArray[i]);
            }
        }

        public void f_createTable_Duoc(string schema)
        {
            string str2 = ("" + "CREATE TABLE " + schema + ".d_dmduocpham( id numeric(6) NOT NULL DEFAULT 0,  ma varchar(20),ten text,  dvt text,  dongia numeric(15,2) DEFAULT 0, ghichu text,  stt int2 DEFAULT 0,tenhoatchat text,  hamluong varchar(20),CONSTRAINT pk_d_dmduocpham PRIMARY KEY (id));\n") + "CREATE TABLE " + schema + ".d_dmkho(id numeric(2) NOT NULL DEFAULT 0,  ten text,  ma varchar(10),CONSTRAINT pk_d_dmkho PRIMARY KEY (id));\n";
            str2 = (str2 + "CREATE TABLE " + schema + ".d_dmkho_vitri(id numeric(4) NOT NULL DEFAULT 0,  ten text,ma varchar(10),  idkho numeric(1) DEFAULT 0,constraint fk_d_dmkho_vitri_d_dmkho foreign key(idkho) references " + schema + ".d_dmkho(id),CONSTRAINT pk_d_dmkho_vitri PRIMARY KEY (id));\n") + "CREATE TABLE " + schema + ".d_dmnhacungcap(id numeric(18) NOT NULL DEFAULT 0,  stt numeric(4) DEFAULT 0,ma varchar(10),  ten text,  diachi text,  dienthoai varchar(50),sotk varchar(50),  masothue varchar(50),  nganhang text,  daidien text,CONSTRAINT pk_d_dmnhacungcap PRIMARY KEY (id));\n";
            str2 = str2 + "CREATE TABLE " + schema + ".d_nhapkho(id numeric(18) NOT NULL DEFAULT 0,  sophieu numeric(10) DEFAULT 0,  ngay timestamp,hoadon varchar(50),  ngayhd timestamp,  tongtien numeric(15,2) DEFAULT 0,tongtiensauthue numeric(15,2) DEFAULT 0,  idnhacungcap numeric(5) DEFAULT 0,loainhap numeric(1) DEFAULT 1,  idkho numeric(2) DEFAULT 0,  nguoigiao text,tenmaytinh text, ngaycapnhat timestamp DEFAULT now(),  iddangnhap numeric(5) DEFAULT 0,ghichu text, CONSTRAINT pk_d_nhapkho PRIMARY KEY (id),CONSTRAINT fk_d_nhapkho_d_dmkho FOREIGN KEY (idkho)  REFERENCES " + schema + ".d_dmkho (id) ,CONSTRAINT fk_d_nhapkho_dmdangnhap FOREIGN KEY (iddangnhap)  REFERENCES " + schema + ".dmdangnhap (id) ,CONSTRAINT fk_d_nhapkho_d_dmnhacungcap FOREIGN KEY (idnhacungcap)  REFERENCES " + schema + ".d_dmnhacungcap (id));\n";
            str2 = str2 + "CREATE TABLE " + schema + ".d_nhapkhoct(id numeric(18) NOT NULL DEFAULT 0,  idnhapkho numeric(18) NOT NULL DEFAULT 0,idduocpham numeric(10) DEFAULT 0,  soluong numeric(10,2) DEFAULT 0,soluong1 numeric(10) DEFAULT 0,  soluong2 numeric(10) DEFAULT 0,giamua numeric(10) DEFAULT 0,  thue numeric(3) DEFAULT 0,giaban numeric(10) DEFAULT 0,  lo varchar(50),  handung timestamp,hangsx text,  tenmaytinh text,  ngaycapnhat timestamp DEFAULT now(),iddangnhap numeric(5) DEFAULT 0,  idvitrikho numeric(4) DEFAULT 0,  soluongxuat numeric(10,2) DEFAULT 0,CONSTRAINT pk_d_nhapkhoct PRIMARY KEY (id),CONSTRAINT fk_d_nhapkhoct_d_dmduocpham FOREIGN KEY (idduocpham)  REFERENCES " + schema + ".d_dmduocpham (id) ,CONSTRAINT fk_d_nhapkhoct_dmdangnhap FOREIGN KEY (iddangnhap)  REFERENCES " + schema + ".dmdangnhap (id) ,CONSTRAINT fk_d_nhapkhoct_d_nhapkho FOREIGN KEY (idnhapkho) REFERENCES " + schema + ".d_nhapkho (id));\n";
            str2 = str2 + "CREATE TABLE " + schema + ".d_theodoi_xuatkho(id numeric(20) NOT NULL DEFAULT 0,  idnhapkhoct numeric(20) NOT NULL DEFAULT 0,idxuatkhoct numeric(20) NOT NULL DEFAULT 0,  idduocpham numeric(10) DEFAULT 0,soluong numeric(10,2) DEFAULT 0,  dongia numeric(15,2) DEFAULT 0,tenmaytinh text,  ngaycapnhat timestamp DEFAULT now(),  iddangnhap numeric(5) DEFAULT 0,idloaixuat numeric(2) DEFAULT 0,CONSTRAINT fk_d_theodoi_xuatkho_d_dmduocpham FOREIGN KEY (idduocpham) REFERENCES " + schema + ".d_dmduocpham (id) ,CONSTRAINT pk_d_theodoi_xuatkhobanlect PRIMARY KEY (id));\n";
            str2 = str2 + "CREATE TABLE " + schema + ".d_xuatkho_banle(id numeric(18) NOT NULL DEFAULT 0,  sophieu numeric(10) DEFAULT 0,ngay timestamp,  tongtien numeric(15,2) DEFAULT 0,  hoten text,idgioitinh numeric(1) DEFAULT 0,  namsinh varchar(4),  diachi text,idloaixuat numeric(2) DEFAULT 1,  ghichu text,  tenmaytinh text,ngaycapnhat timestamp DEFAULT now(),  iddangnhap numeric(5) DEFAULT 0, chietkhau numeric(5,2) DEFAULT 0,  idnhomkh numeric(2) DEFAULT 1,  solanin numeric(3) DEFAULT 0,CONSTRAINT fk_d_xuatkho_banle_dmdangnhap FOREIGN KEY (iddangnhap)  REFERENCES " + schema + ".dmdangnhap (id) ,CONSTRAINT pk_d_xuatkho_banle PRIMARY KEY (id));\n";
            str2 = str2 + "CREATE TABLE " + schema + ".d_xuatkho_banlect(id numeric(20) NOT NULL DEFAULT 0,  idxuatkhobanle numeric(20) NOT NULL DEFAULT 0,idduocpham numeric(10) DEFAULT 0,  soluong numeric(10,2) DEFAULT 0,dongia numeric(15,2) DEFAULT 0,  idnguoichidinh numeric(3) DEFAULT 0,tenmaytinh text,  ngaycapnhat timestamp DEFAULT now(),iddangnhap numeric(5) DEFAULT 0,  thue numeric(5,2) DEFAULT 0,CONSTRAINT pk_d_xuatkho_banlect PRIMARY KEY (id),CONSTRAINT fk_d_xuatkho_banlect_d_dmduocpham FOREIGN KEY (idduocpham) REFERENCES " + schema + ".d_dmduocpham (id) ,CONSTRAINT fk_d_xuatkho_banlect_dmdangnhap FOREIGN KEY (iddangnhap)  REFERENCES " + schema + ".dmdangnhap (id) ,CONSTRAINT fk_d_xuatkho_banlect_d_xuatkho_banle FOREIGN KEY (idxuatkhobanle) REFERENCES " + schema + ".d_xuatkho_banle (id));\n";
            str2 = str2 + "CREATE TABLE " + schema + ".d_toathuoc (id numeric(10,0) NOT NULL default 0,idql numeric(15,0)  default 0,mabn varchar(10),ngay timestamp without time zone,chandoan text,idphong  numeric(2,0)  default 0,ghichu text,idbacsi  numeric(2,0)  default 0,iddangnhap numeric(3,0) default 0,tenmaytinh text,ngaycn timestamp default now(), CONSTRAINT pk_" + schema + "_d_toathuoc PRIMARY KEY (id));\n";
            string[] strArray = (str2 + "CREATE TABLE " + schema + ".d_toathuocct (id numeric(12,0) NOT NULL default 0,idtoathuoc numeric(10,0)  default 0,iddp numeric(10,0)  default 0,soluongsang numeric(5,2)  default 0,soluongtrua numeric(5,2)  default 0,soluongchieu  numeric(5,2)  default 0,soluongtoi numeric(5,2)  default 0,soluong   numeric(5,2)  default 0,songay  numeric(3,0)  default 0,dongia numeric(15,2)  default 0,cachdung text,dadongtien numeric(1,0)  default 0,idttvp varchar(200),sldathanhtoan numeric(15,2)  default 0,iddangnhap numeric(3,0) default 0,tenmaytinh text,ngaycn timestamp default now(), CONSTRAINT pk_" + schema + "_d_toathuocct PRIMARY KEY (id), CONSTRAINT fk_" + schema + "_d_toathuocct_d_toathuoc FOREIGN KEY (idtoathuoc) REFERENCES " + schema + ".d_toathuoc(id));\n").Split(new char[] { ';' });
            for (int i = 0; i < strArray.Length; i++)
            {
                this.f_update(strArray[i]);
            }
        }

        public void f_createTable_macdinh(string schema)
        {
            string str = "";
            string[] strArray = ((str + "CREATE TABLE " + schema + ".dsthongso (id numeric(10,0) NOT NULL,stt  numeric(3,0) NOT NULL default 0,ten  text,giatri1 text,giatri2 text,ngaycn timestamp without time zone  default now(), CONSTRAINT pk_dsthongso PRIMARY KEY (id, stt));\n") + "CREATE TABLE " + schema + ".dsthongso(id numeric(10) NOT NULL,  stt numeric(10) NOT NULL DEFAULT 0,  ten text,giatri1 text,  giatri2 text,  ngaycn timestamp DEFAULT now(),CONSTRAINT pk_dsthongso PRIMARY KEY (id, stt));\n").Split(new char[] { ';' });
            for (int i = 0; i < strArray.Length; i++)
            {
                this.f_update(strArray[i]);
            }
        }

        public void f_createTable_vienphi(string schema)
        {
            string str = "";
            string str2 = str + "CREATE TABLE " + schema + ".vp_dmvp(id numeric(6) NOT NULL DEFAULT 0,  ma varchar(6),  ten text,dvt text,  dongia numeric(15,2) DEFAULT 0,  ghichu text,  stt int2 DEFAULT 0,  nhapdongia numeric(1) DEFAULT 0,CONSTRAINT pk_vp_dmvp PRIMARY KEY (id));\n";
            str2 = str2 + "CREATE TABLE " + schema + ".vp_hoantrahoadon (id numeric(20,0) NOT NULL default 0,idttvp  numeric(20,0)  default 0,ngay  timestamp without time zone  default now(),tenmaytinh  text,ngaycapnhat  timestamp without time zone  default now(), CONSTRAINT pk_" + schema + "_vp_hoantrahoadon PRIMARY KEY (id));\n";
            str2 = str2 + "CREATE TABLE " + schema + ".vp_ttvp (id numeric(20,0) NOT NULL default 0,idql  numeric(15,0)  default 0,hoadon varchar(50),soluutru  varchar(50),sobienlai varchar(50),tongtien numeric(15,2)  default 0,thucthu  numeric(15,2)  default 0,ngaythu  timestamp without time zone,ghichu  text,tenmaytinh  text,ngaycapnhat timestamp without time zone  default now(),hoantra numeric(1,0)  default 0, CONSTRAINT pk_" + schema + "_vp_ttvp PRIMARY KEY (id));\n";
            string[] strArray = (str2 + "CREATE TABLE " + schema + ".vp_ttvpct (id  numeric(20,0) NOT NULL default 0,idttvp  numeric(20,0)  default 0,idnoidung numeric(10,0)  default 0,manoidung  varchar(20),tennoidung  text,dvt  text,loainoidung numeric(1,0)  default 0,soluongthu numeric(10,2)  default 0,dongia numeric(15,2)  default 0,thucthu numeric(15,2)  default 0,idchidinh  numeric(20,0)  default 0,CONSTRAINT pk_" + schema + "_vp_ttvpct PRIMARY KEY (id), CONSTRAINT fk_" + schema + "vp_ttvpct_vp_ttvp FOREIGN KEY (idttvp) REFERENCES " + schema + ".vp_ttvp(id));\n").Split(new char[] { ';' });
            for (int i = 0; i < strArray.Length; i++)
            {
                this.f_update(strArray[i]);
            }
        }

        private bool f_update(string sql)
        {
            bool flag;
            NpgsqlConnection connection = new NpgsqlConnection(this._condata.StringConnect);
            connection.Open();
            NpgsqlCommand command = new NpgsqlCommand(sql, connection);
            try
            {
                flag = command.ExecuteNonQuery() >= 0;
            }
            catch
            {
                flag = false;
            }
            finally
            {
                connection.Close();
                command = null;
            }
            return flag;
        }
    }
}

